﻿Imports System.Data.SqlClient
Imports System.IO
Imports System.Drawing

Public Class _Default
    Inherits System.Web.UI.Page
    Dim objDB As New DBConnect
    Dim myDS As DataSet
    Dim con As New DBConnect
    Public Shared startTermID As Integer
    Public Shared endTermID As Integer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ''check if user signed in if not going back to login page 
        If Not Session.Item("UserIn").Equals("yes") Then
            Response.Redirect("default.aspx")
        End If

        'assignning start adn end Term 
        Dim SqlConnectString2 As String = con.SqlConnectString
        Dim myConnection2 As New SqlConnection(SqlConnectString2)
        Dim myCommand2 As New SqlCommand()
        myCommand2.Connection = myConnection2
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand2.CommandType = CommandType.StoredProcedure
        myCommand2.CommandText = "GetStartID"       'Name of the Stored Procedure to execute
        Dim returnParameter2 As New SqlParameter("@StartID", 0)
        returnParameter2.Direction = ParameterDirection.Output
        myCommand2.Parameters.Add(returnParameter2)

        Try
            myConnection2.Open()

        Catch ex As Exception
            Throw ex
        Finally
            startTermID = CInt(myCommand2.ExecuteScalar())
            myConnection2.Close()
            myConnection2.Dispose()
        End Try
        'end term id
        Dim myConnection3 As New SqlConnection(SqlConnectString2)
        Dim myCommand3 As New SqlCommand()
        myCommand3.Connection = myConnection3
        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand3.CommandType = CommandType.StoredProcedure
        myCommand3.CommandText = "GetEndID"       'Name of the Stored Procedure to execute
        Dim returnParameter3 As New SqlParameter("@EndID", 0)
        returnParameter3.Direction = ParameterDirection.Output
        myCommand3.Parameters.Add(returnParameter3)

        Try
            myConnection3.Open()

        Catch ex As Exception
            Throw ex
        Finally
            endTermID = CInt(myCommand3.ExecuteScalar())
            myConnection3.Close()
            myConnection3.Dispose()
        End Try


        If Not IsPostBack Then
            Dim test As New Parameter
            test.Type = TypeCode.Int32
            test.DefaultValue = 1
            ViewStudentGV.FilterParameters.Item(1) = test
            Dim test2 As New Parameter
            test2.Type = TypeCode.Int32
            test2.DefaultValue = 999
            ViewStudentGV.FilterParameters.Item(3) = test2
            BindSampleData()
        End If
    End Sub

    ' Gets and Sets the current state of SortDirection
    Public Property SortDirection() As SortDirection
        Get
            If ViewState("SortDirection") Is Nothing Then
                ViewState("SortDirection") = SortDirection.Descending
            End If
            Return DirectCast(ViewState("SortDirection"), SortDirection)
        End Get
        Set(value As SortDirection)
            ViewState("SortDirection") = value
        End Set
    End Property

    ' This method will sort the gridview based on which column header is clicked
    Protected Sub SortGridView(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim mySortExpression As String = e.SortExpression   ' Column to be sorted
        Dim newSortDirection As String = String.Empty       ' Column's sort direction

        ' If the current state SortDirection is Ascending, set it to Descending and vice versa
        ' Then, set the newSortDirection to DESC or ASC based on current SortDirection
        If SortDirection = SortDirection.Ascending Then
            SortDirection = SortDirection.Descending
            newSortDirection = "DESC"
        Else
            SortDirection = SortDirection.Ascending
            newSortDirection = "ASC"
        End If

        ' Get the data from the database
        ' Sort the data, based selected column (mySortExpression), by newSortDirection (ASC/DESC)
        ' Then bind the sorted data to the GV
        myDS = GetSampleData()
        myDS.Tables(0).DefaultView.Sort = mySortExpression & " " & newSortDirection
        'gvStudentList.DataSource = myDS.Tables(0)
        gvStudentList.DataBind()

    End Sub

    Private Sub BindSampleData()
        'gvStudentList.DataBind()
        If gvStudentList.Rows.Count = 0 Then
            btnExport.Visible = False
        End If
    End Sub


    Private Function GetSampleData() As DataSet
        Dim strSql As String = "SELECT i.tuID, u.firstName, u.lastName, u.email, d.DepartmentName, o.organizationName, i.classification, i.role, i.startTerm, i.endTerm, i.Comments FROM Involvments i INNER JOIN Users u ON i.tuID = u.ID INNER JOIN Departments d ON i.departmentID = d.DepartmentID INNER JOIN Organizations o ON i.organizationID = o.organizationID;"
        myDS = objDB.getDataSet(strSql)
        Return myDS
    End Function

    'Deleting Admin From Database fired up when user clicks delete
    Protected Sub gvAdminList_RowDeleting(sender As Object, e As GridViewDeleteEventArgs) Handles gvStudentList.RowDeleting

        Dim rowIndex As Integer = e.RowIndex ' 0, 1, 2,  ....
        Dim id As Integer = CInt(gvStudentList.DataKeys(rowIndex).Value) ' tuID value
        Dim NetId As String = CStr(gvStudentList.Rows(rowIndex).Cells(0).Text) ' same as id, but string


        'connecting to database 
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        myCommand.Connection = myConnection

        'Set the SQLCommand object's properties for executing a Stored Procedure
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "RemoveInvolvement"       'Name of the Stored Procedure to execute

        Dim inputParameter As New SqlParameter("InvolvementID", id)
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)

        Dim returnParameter As New SqlParameter("@new_identity", 0)
        returnParameter.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(returnParameter)
        Try
            myConnection.Open()
            myCommand.ExecuteScalar()

        Catch ex As Exception
            Throw ex
            ' commented this out because it was throwing errors and preventing code from compiling, could add later
            'Finally
            '    If Not IsDBNull(myCommand.Parameters("@new_identity").Value) Then
            '        result = CType(myCommand.Parameters("@new_identity").Value, Integer)

            '        If result = 0 Then
            '            lblMsg.Visible = True
            '            lblMsg.Text = "Success! User ID: " + NetId + " has been Removed.If its mistake add the Administrator again"
            '            bindGridView()
            '        Else
            '            lblMsg.Visible = True
            '            lblMsg.Text = "Sorry but there was an error try refreshing page and try again"
            '        End If
            '    Else
            '        bindGridView()
            '        lblMsg.Visible = True
            '        lblMsg.Text = "Success! User ID: " + NetId + " has been Removed.If its mistake add the Administrator again"
            'End If
            myConnection.Close()
            myConnection.Dispose()
        End Try
    End Sub

    Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=StudentList.xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Using sw As New StringWriter()
            Dim hw As New HtmlTextWriter(sw)

            'To Export all pages
            gvStudentList.AllowPaging = False
            gvStudentList.DataBind()

            gvStudentList.HeaderRow.BackColor = Color.White
            For Each cell As TableCell In gvStudentList.HeaderRow.Cells
                cell.BackColor = gvStudentList.HeaderStyle.BackColor
            Next
            For Each row As GridViewRow In gvStudentList.Rows
                row.BackColor = Color.White
                For Each cell As TableCell In row.Cells
                    If row.RowIndex Mod 2 = 0 Then
                        cell.BackColor = gvStudentList.AlternatingRowStyle.BackColor
                    Else
                        cell.BackColor = gvStudentList.RowStyle.BackColor
                    End If
                    cell.CssClass = "textmode"
                Next
            Next

            gvStudentList.RenderControl(hw)
            'style to format numbers to string
            Dim style As String = "<style> .textmode { } </style>"
            Response.Write(style)
            Response.Output.Write(sw.ToString())
            Response.Flush()
            Response.[End]()
        End Using
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Verifies that the control is rendered
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim thisStartTermID As Integer
        Dim thisEndTermID As Integer

        If CInt(ddStartTerm.SelectedIndex) = 0 Then
            thisStartTermID = 1
        Else
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myCommandStartTerm As New SqlCommand
            Dim myConnectionStartTerm As New SqlConnection(SqlConnectString)
            Dim studentStartTermID As Integer
            myConnectionStartTerm.Open()
            myCommandStartTerm.Connection = myConnectionStartTerm
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommandStartTerm.CommandType = CommandType.StoredProcedure
            myCommandStartTerm.CommandText = "GetTermID" 'Name of the Stored Procedure to execute
            Dim inputParamStartTerm As New SqlParameter("@TermName", CStr(ddStartTerm.SelectedItem.Value))
            inputParamStartTerm.Direction = ParameterDirection.Input
            myCommandStartTerm.Parameters.Add(inputParamStartTerm)
            Dim returnParamStartTerm As New SqlParameter("@TermID", 0)
            returnParamStartTerm.Direction = ParameterDirection.Output
            myCommandStartTerm.Parameters.Add(returnParamStartTerm)

            Try
                studentStartTermID = CInt(myCommandStartTerm.ExecuteScalar())
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            thisStartTermID = studentStartTermID
        End If

        If CInt(ddEndTerm.SelectedIndex) = 0 Then
            thisEndTermID = 999
        Else
            'SelectedItem.Value
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myCommandEndTerm As New SqlCommand
            Dim myConnectionEndTerm As New SqlConnection(SqlConnectString)
            Dim studentEndTermID As Integer
            myConnectionEndTerm.Open()
            myCommandEndTerm.Connection = myConnectionEndTerm
            'Set the SQLCommand object's properties for executing a Stored Procedure
            myCommandEndTerm.CommandType = CommandType.StoredProcedure
            myCommandEndTerm.CommandText = "GetTermID" 'Name of the Stored Procedure to execute
            Dim inputParamEndTerm As New SqlParameter("@TermName", CStr(ddStartTerm.SelectedItem.Value))
            inputParamEndTerm.Direction = ParameterDirection.Input
            myCommandEndTerm.Parameters.Add(inputParamEndTerm)
            Dim returnParamEndTerm As New SqlParameter("@TermID", 0)
            returnParamEndTerm.Direction = ParameterDirection.Output
            myCommandEndTerm.Parameters.Add(returnParamEndTerm)

            Try
                studentEndTermID = CInt(myCommandEndTerm.ExecuteScalar())
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            thisEndTermID = studentEndTermID
        End If

        Dim test As New Parameter
        test.Type = TypeCode.Int32
        test.DefaultValue = thisStartTermID
        ViewStudentGV.FilterParameters.Item(1) = test

        Dim intTest2 As Integer = 1
        Dim test2 As New Parameter
        test2.Type = TypeCode.Int32
        test2.DefaultValue = thisEndTermID
        ViewStudentGV.FilterParameters.Item(3) = test2
    End Sub

    Protected Sub ClearButton_Click(sender As Object, e As EventArgs) Handles ClearButton.Click
        ddDepartment.SelectedIndex = 0
        ddEndTerm.SelectedIndex = 0
        ddOrganization.SelectedIndex = 0
        ddStartTerm.SelectedIndex = 0
        txtFirstName.Text = ""
        txtLastName.Text = ""
        txtTUID.Text = ""
        txtUserID.Text = ""
        'gvStudentList.Sort("", SortDirection.Ascending)
        'ViewState("SortDirection") = SortDirection.Descending
        gvStudentList.DataBind()
    End Sub


End Class